package weaver.formmode.interfaces.impl.huangwm.SW003;

import com.alibaba.fastjson.JSONObject;
import com.api.customization.huangwm.dao.SW003.LoggingDao;
import com.customization.huangwm.utils.DBTools;
import com.customization.huangwm.utils.DateTools;
import com.customization.huangwm.utils.HttpRequest;
import com.customization.huangwm.utils.LogTool;
import com.engine.personalIncomeTax.biz.RecordsBiz;
import jxl.read.biff.Record;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.file.ExcelParseForPOI;
import weaver.formmode.interfaces.ImportPreInterfaceForPOIAction;
import weaver.general.BaseBean;
import weaver.general.Util;
import weaver.hrm.User;
import weaver.system.SysRemindWorkflow;

import java.util.*;

/**
 * @Author Huangwm
 * @Date 2022-10-13
 * @Version 1.0
 * @Description TODO 仓库库存导入动作 导入前校验、更新物料檔案庫存及传入期初库存
 **/
public class ImportWareHouseBeforeAction implements ImportPreInterfaceForPOIAction {
    private LogTool logTool = new LogTool("/log/SW003/huangwm/model/action/ImportWareHouseBeforeAction", false);
    private DBTools dbTools = new DBTools();
    private DateTools dateTools = new DateTools();
    private LoggingDao loggingDao = new LoggingDao();

    @Override
    public String checkImportData(Map<String, Object> param, User user, ExcelParseForPOI excel) {
        Integer importtype = Util.getIntValue(param.get("importtype").toString());
        // 获取模块ID
        Integer modeId = Util.getIntValue(param.get("modeid").toString());
        //表单id
        Integer formId = Util.getIntValue(param.get("formid").toString());
        // 获取当前登录人员ID
        Integer userId = user.getUID();
        logTool.writeLog("倉庫庫存导入前执行校验---");
        Workbook workbook = excel.getWb();
        Sheet sheet = workbook.getSheetAt(0);//獲取第一個sheet頁
        //获取最后一行的num，即总行数。此处从0开始计数
        int maxRow = sheet.getLastRowNum();
        List<List<String>> Excel_datas = new ArrayList<>();
        List<String> codes = new ArrayList<>();//單獨存放物料編碼
        BaseBean baseBean = new BaseBean();
        String eop_reqUrl = baseBean.getPropValue("SW003", "eop_reqUrl");
        if (eop_reqUrl.isEmpty()) {
            return "獲取SW003.peoperties中的eop_reqUrl爲空";
        }
        String eop_reqToken = baseBean.getPropValue("SW003", "eop_reqToken");
        if (eop_reqToken.isEmpty()) {
            return "獲取SW003.peoperties中的eop_reqToken爲空";
        }

        if (!(sheet.getRow(0).getCell(1) + "").equals("物料編碼")) {
            return "Excel模板格式錯誤,第二列應為物料編碼,現為[" + sheet.getRow(0).getCell(1) + "]";
        }
        if (!(sheet.getRow(0).getCell(2) + "").equals("倉庫")) {
            return "Excel模板格式錯誤,第三列應為倉庫,現為[" + sheet.getRow(0).getCell(2) + "]";
        }
        if (!(sheet.getRow(0).getCell(3) + "").equals("庫存數量")) {
            return "Excel模板格式錯誤,第四列應為庫存數量,現為[" + sheet.getRow(0).getCell(3) + "]";
        }

        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);

        try {
            for (int row = 1; row <= maxRow; row++) {
                List<String> data = new ArrayList<>();
                //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                int maxCel = sheet.getRow(row).getLastCellNum();
                logTool.writeLog("--------第" + row + "行的数据如下--------");
                for (int cel = 1; cel <= 3; cel++) {
                    //第一列和第二列是編碼和倉庫 讀取是轉成字符串 第三列數量
                    if (cel == 1) {
                        sheet.getRow(row).getCell(cel).setCellType(CellType.STRING);
                        if (!isBlank(sheet.getRow(row).getCell(cel) + "").isEmpty()) {
                            if(!codes.contains(sheet.getRow(row).getCell(cel).getStringCellValue())){
                                codes.add(sheet.getRow(row).getCell(cel).getStringCellValue());
                            }
                        } else {
                            return ("第" + row + "行物料編碼存在空值");
                        }
                        data.add(isBlank(sheet.getRow(row).getCell(cel).getStringCellValue() + ""));
                    }else if (cel == 3) {
                        sheet.getRow(row).getCell(cel).setCellType(CellType.STRING);
                        data.add(String.valueOf(sheet.getRow(row).getCell(cel).getStringCellValue()));
                    }else {
                        sheet.getRow(row).getCell(cel).setCellType(CellType.STRING);
                        data.add(isBlank(sheet.getRow(row).getCell(cel).getStringCellValue() + ""));
                    }
                }
                logTool.writeLog(JSONObject.toJSONString(data));
                Excel_datas.add(data);
            }
            if (codes.size() > 0 && Excel_datas.size() > 0) {
                String check = check2(codes);
                if (check.indexOf("false")>=0) {
                    return check;
                }
                //檢查是否導入過相同物料和倉庫的記錄
                List<String> manageDep = new ArrayList<>();
                List<Map<String,String>> InventoryLog = new ArrayList<>();
                RecordSet get = new RecordSet();
                int row = 2;
                for (List<String> value : Excel_datas) {
                    String assetCode = value.get(0);//物料編碼
                    String _assetCode = dbTools.getAssetidByCode(get,assetCode);//物料id
                    Map<String,String> assetInfo = dbTools.getAssetAllFieldoByCode(get,assetCode);//物料信息
                    String _manageDep = dbTools.getAssetInfoByCode(get,assetCode,"manageDep");
                    if(!manageDep.contains(_manageDep)){
                        manageDep.add(_manageDep);
                    }
                    if(_assetCode.indexOf("不存在") >= 0 || _assetCode.isEmpty()){
                        return "第" + row + "行物料編碼["+assetCode+"]不存在";
                    }
                    logTool.writeLog("管理部门>"+ manageDep.toString());
                    if(manageDep.size()>1){
                        return "第" + row + "行物料所屬管理部門與其他行不同,請勿一次性導入不同部門庫存";
                    }

                    String wareHouse = value.get(1);//倉庫名稱
                    String _wareHouse = dbTools.exisWarehouse(get,wareHouse);
                    if (_wareHouse.indexOf("不存在") >= 0) {
                        return "第" + row + "行" + _wareHouse;
                    }
                    boolean exists = getExists(get,_assetCode,_wareHouse);
                    if(exists){
                        return "第" + row + "行物料編碼和倉庫已導入過庫存請務重複導入";
                    }
                    Double num1 = Double.valueOf(value.get(2));//入庫数量
                    //記錄入庫
                    Map<String,String> loginfo = new HashMap<>();
                    loginfo.put("source","0");
                    loginfo.put("assetcode",assetCode);
                    loginfo.put("assetname",assetInfo.get("assetname"));
                    loginfo.put("num1",String.valueOf(num1));
                    loginfo.put("number","impWareHouse_"+userId);
                    loginfo.put("warehouse",_wareHouse);
                    InventoryLog.add(loginfo);

                    row ++;
                }

                //合并同一个code的数量
                List<List<String>> newlists = new ArrayList<>();
                for(int i=0;i<codes.size();i++){
                    String code = codes.get(i);
                    List<String> newlist = new ArrayList<>();
                    newlist.add(code);
                    Double num = 0.00;
                    for(int j=0;j<Excel_datas.size();j++){
                        List<String> temp = Excel_datas.get(j);
                        String temp_code = temp.get(0);
                        Double temp_num = Double.valueOf(temp.get(2));
                        if(code.equals(temp_code)){
                            num = num+temp_num;
                        }
                    }
                    newlist.add(Double.toString(num));
                    newlists.add(newlist);
                }
                //2023年10月18日 取消传入yy
                /*
                HttpRequest httpRequest = new HttpRequest();
                String times = httpRequest.getTimestamp();
                //寫入用友期初庫存
                JSONObject params_Bill = new JSONObject();
                params_Bill.put("reqNb", times);
                params_Bill.put("reqLang", "zh");
                params_Bill.put("reqToken", eop_reqToken);
                Map<String, Object> reqData_Bill = new HashMap<>();
                List<Map<String, Object>> bill = new ArrayList<>();//reqData中的bill
                Map<String, Object> bill_data = new HashMap<>();
                //billHead内容
                Map<String, Object> billHead = new HashMap<>();
                billHead.put("id",times);
                billHead.put("cbilltypecode","4A");
                billHead.put("dbilldate",dateTools.getCurrentDate());
                billHead.put("pk_corp","MW");
                billHead.put("vbillcode","");
                billHead.put("cdispatcherid","21");
                billHead.put("cdeptid",dbTools.getYyDepCode(user.getUID() + ""));
                billHead.put("pk_calbody","MW");
                String cwarehouseid = dbTools.getWarehouse(manageDep.get(0));
                billHead.put("cwarehouseid",cwarehouseid);//根據管理部門獲取對應的用友倉庫key
                billHead.put("coperatorid",dbTools.getWorkCode(user.getUID()+""));
                billHead.put("fbillflag","2");
                billHead.put("vnote","期初-倉庫库存导入-"+cwarehouseid);
                //添加billhead内容
                bill_data.put("billHead",billHead);

                List<Map<String, Object>> billBody = new ArrayList<>();
                RecordSet rs1 = new RecordSet();
                boolean f = true ;

                List<String> existsItem = getExistsItem();//不需要导入期初
                for (List<String> value : newlists) {

                    String assetCode = value.get(0);//物料編碼
                    Double totalquantity = Double.parseDouble(value.get(1));//总入库数量
                    Double currQuantity = getAssetCounts(rs1, assetCode);
                    Double total = totalquantity + currQuantity;
                    //更新物料庫存
                    f = updateAssetCounts(rst, assetCode, total);
                    if (!f) {
                        rst.rollback();
                        return ("物料編碼[" + assetCode + "]更新庫存為" + String.valueOf(total) + "失敗");
                    }
                    if(!existsItem.contains(assetCode)) {
                        Map<String, Object> billBody_data = new HashMap<>();
                        billBody_data.put("cinventoryid", assetCode);
                        billBody_data.put("dbizdate", dateTools.getCurrentDate());
                        billBody_data.put("ninnum", totalquantity);
                        billBody_data.put("nprice", "0");
                        billBody_data.put("nmny", "0");
                        //添加body内容
                        billBody.add(billBody_data);

                    }
                }
                bill_data.put("billBody",billBody);
                bill.add(bill_data);
                reqData_Bill.put("bill",bill);
                params_Bill.put("reqData",reqData_Bill);
                if(billBody.size()>0){
                    logTool.writeLog("---期初入庫傳入用友參數如下---");
                    logTool.writeLog(JSONObject.toJSONString(params_Bill));
                    JSONObject json_Bill = httpRequest.CallApiPost(eop_reqUrl + "/eop/eapToUfida/icBillBegin", JSONObject.toJSONString(params_Bill));
                    logTool.writeLog("---期初入庫傳入用友結果如下---");
                    logTool.writeLog(JSONObject.toJSONString(json_Bill));
                    loggingDao.wrtieLog("0", "/eop/eapToUfida/icBillBegin", JSONObject.toJSONString(params_Bill), JSONObject.toJSONString(json_Bill));
                    if(json_Bill==null || !json_Bill.getOrDefault("state","-1").equals("0") || json_Bill.getString("state").equals("-1")){
                        logTool.writeLog("期初入庫失敗 " + JSONObject.toJSONString(json_Bill));
                        rst.rollback();
                        SysRemindWorkflow remind = new SysRemindWorkflow();
                        remind.make("EAP導入物料期初庫存對接失敗",
                                0, 0, 0, 0, 1,
                                dbTools.getUseridByLoginid("hel"),
                                "<span>EAP導入倉庫庫存&用友期初庫存入库對接失敗 " + JSONObject.toJSONString(json_Bill)
                                        + "; </span><br />详细看查看日志表uf_003_interfaceLog或当天" + dateTools.getCurrentDate() + "的对接日并搜索关键字：" + times, 0);

                        return ("<span>EAP導入倉庫庫存&用友期初庫存入库對接失敗 " + JSONObject.toJSONString(json_Bill)
                                + "; </span><br />详细看查看日志表uf_003_interfaceLog或当天" + dateTools.getCurrentDate() + "的对接日并搜索关键字：" + times);
                    }
                }else{
                    logTool.writeLog("本次導入不需要傳入用友期初");
                }
                 */
                //2023年12月15日 保留OA的库存更新
                RecordSet rs1 = new RecordSet();
                boolean f = true ;
                for (List<String> value : newlists) {

                    String assetCode = value.get(0);//物料編碼
                    Double totalquantity = Double.parseDouble(value.get(1));//总入库数量
                    Double currQuantity = getAssetCounts(rs1, assetCode);
                    Double total = totalquantity + currQuantity;
                    //更新物料庫存
                    f = updateAssetCounts(rst, assetCode, total);
                    if (!f) {
                        rst.rollback();
                        return ("物料編碼[" + assetCode + "]更新庫存為" + String.valueOf(total) + "失敗");
                    }
                }

                //入庫日志
                for(int i=0;i<InventoryLog.size();i++){
                    Map<String,String> log = InventoryLog.get(i);
                    loggingDao.wrtieInventoryLog(rst,"0", log.get("assetcode"),
                            Double.parseDouble(log.get("num1")), log.get("number"),
                            "倉庫庫存導入",log.get("warehouse"),"",
                            "4","",userId+"",log.get("assetname"),"","");
                }

                rst.commit();

            }else{
                return ("擴展接口獲取數據爲空 ");
            }
        } catch (Exception e) {
            rst.rollback();
            e.printStackTrace();
            logTool.writeLog("導入時獲取表格數據異常 "+e.getMessage());
            return ("導入時獲取表格數據異常 "+e.getMessage());
        }
        return null;
    }

    public String isBlank(String str) {
        if (str.isEmpty() || str.equals("null")) return "";
        return str;
    }
    /**
     * 检查本次导入的数据中是否存在相同的itemcode
     * @param codes
     * @return
     */
    public String check2(List<String> codes) {
        if (codes.size() <= 0) return "fasle，物料編碼集合为空";
        RecordSet rs = new RecordSet();
        int i = 2;
        for (String code : codes) {
            rs.execute("select id from uf_003_AssteInfo where assetCode='" + code + "'");
            if (!rs.next()) {
                return "fasle，第" + i + "行物料編碼不存在";
            }
        }
        return "check2";
    }

    /**
     * 獲取物料当前的库存
     * @param rs
     * @param assetCode
     * @return
     */
    public Double getAssetCounts(RecordSet rs,String assetCode){
        if(assetCode.isEmpty()) return 0.00;
        rs.execute("select totalQuantity from uf_003_AssteInfo where assetCode='"+assetCode+"'");
        rs.next();
        return rs.getDouble("totalQuantity");
    }

    /**
     * 导入仓库库存后更新物料档案中的库存信息
     * @param rs
     * @param assetCode
     * @param total
     * @return
     */
    public boolean updateAssetCounts(RecordSetTrans rs, String assetCode, Double total) throws Exception {
        String sql = "update uf_003_AssteInfo set totalQuantity="+total+",InventoryQuantity="+total+"-NVL(usedQuantity,0) where assetCode='"+assetCode+"'";
        boolean result =  rs.execute(sql);
        logTool.writeLog("導入倉庫庫存后更新檔案庫存["+sql+"] result="+result);
        return result;
    }

    /**
     * 根據物料id跟倉庫id判斷是否存在同記錄庫存
     * @param rs
     * @param assetCode
     * @param wareHouse
     * @return
     */
    public boolean getExists(RecordSet rs,String assetCode,String wareHouse){
        String sql = "select id from uf_003_WarehouseNum where assetcode ="+assetCode+" and wareHouse ="+wareHouse;
        return  rs.execute(sql) && rs.next();
    }

    /**
     * 不需要导入期初的item
     * @return
     */
    public List<String> getExistsItem(){
        List<String> list = new ArrayList<>();
        String sql = "select * from uf_003_existsItem ";
        RecordSet rs = new RecordSet();
        rs.execute(sql);
        while (rs.next()){
            list.add(rs.getString("item"));
        }
        return list;
    }
}
